package com.kuang.builder;



import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.meta.Column;
import cn.hutool.json.JSONUtil;
import com.kuang.bean.SqlFieldInfo;
import com.kuang.bean.SqlTableInfo;
import com.kuang.constants.SqlConstants;
import com.kuang.constants.SqlTableConstants;
import com.kuang.constants.SqlTypeConstants;
import com.kuang.utils.JDBCUtils;
import com.kuang.utils.SqlTypeToJavaTypeUtils;
import lombok.extern.slf4j.Slf4j;
import sun.reflect.FieldInfo;


import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;


/**
 * @ClassName BuildTable
 * @Description 读取数据库中的表
 * @Author Augenestern_QXL
 * @Date 2024/2/18
 * @Version 1.0
 */
@Slf4j
public class BuildTable {

    /**
     * 数据库连接对象conn
     */
    private static Connection conn;
    /**
     * PreparedStatement执行sql语句
     */
    private static PreparedStatement ps = null;
    /**
     * resultSet 返回的结果集，结果集中封装了我们全部的查询出来的结果
     */
    private static ResultSet rs = null;



    /**
     * 读取表的基本信息
     */
    public static List<SqlTableInfo> getTables() {
        List<SqlTableInfo> sqlTableInfoList = new ArrayList();
        try {
            //1.获取数据库连接
            conn = JDBCUtils.getConnection();
            //2.获取prepareStatement对象
            ps = conn.prepareStatement(SqlConstants.SQL_SHOW_TABLES_STATUS);
            //3.执行sql语句
            rs = ps.executeQuery();
            //4.遍历查询结果,获取表名和表注释
            while(rs.next()) {
                // 获取表名
                String tableName = rs.getString(SqlTableConstants.TABLE_NAME);
                // 获取表注释
                String comment = rs.getString(SqlTableConstants.TABLE_COMMENT);
                // 将bean的名字设置为表名
                String beanName = tableName;
                // 如果忽略数据库表的前缀(例如:review_springboot -> springboot)
                if(SqlTableConstants.IGNORE_TABLE_PREFIX){
                    // 截取分隔字符串之后的字符串，不包括分隔字符串
                    beanName = StrUtil.subAfter(tableName,"_",false);
                }
                // 大写beanName的首字母()
                beanName = StrUtil.upperFirst(beanName);

                // 创建数据库表信息类
                SqlTableInfo sqlTableInfo = new SqlTableInfo();
                // 设置数据库表名
                sqlTableInfo.setTableName(tableName);
                // 设置数据库bean名
                sqlTableInfo.setBeanName(beanName);
                // 设置表备注
                sqlTableInfo.setComment(comment);
                // 后缀参数(例如:Springboot_userQuery)
                sqlTableInfo.setBeanParamName(beanName + SqlTableConstants.SUFFIX_BEAN_PARAM);

                // 读取表的字段信息
                readFieldInfo(sqlTableInfo);
                // 读取表索引
                getKeyIndexInfo(sqlTableInfo);
                //log.info("表的全部信息:{}", JSONUtil.toJsonPrettyStr(sqlTableInfo));
                sqlTableInfoList.add(sqlTableInfo);

            }
        } catch (Exception e) {
            log.error("读取表失败",e);
        } finally {
            // 释放资源
            JDBCUtils.close(conn,ps,rs);
        }
        return sqlTableInfoList;
    }


    /**
     * 读取表的字段信息
     * @param sqlTableInfo
     * @return
     */
    private static void readFieldInfo(SqlTableInfo sqlTableInfo) {
        List<SqlFieldInfo> sqlFieldInfoList = new ArrayList();
        try {
            //1.获取数据库连接
            conn = JDBCUtils.getConnection();
            //2.获取prepareStatement对象
            ps = conn.prepareStatement(StrUtil.format(SqlConstants.SQL_SHOW_FULL_FIELDS,sqlTableInfo.getTableName()));
            //3.执行sql语句
            rs = ps.executeQuery();
            //4.遍历查询结果,获取表字段
            while(rs.next()) {
                String field = rs.getString("field");
                String type = rs.getString("type");
                String extra = rs.getString("extra");
                String comment = rs.getString("comment");
                // 截取分隔字符串之前的字符串(示例: varchar(50) -> varchar)
                type = StrUtil.subBefore(type,"(",false);
                // 将下划线方式命名的字符串转换为驼峰式(示例:springboot_id) -> springbootId)
                String propertyName = StrUtil.toCamelCase(field);
                //log.info("propertyName:{}  type:{}  extra:{}  comment:{}",propertyName,type,extra,comment);

                SqlFieldInfo sqlFieldInfo = new SqlFieldInfo();
                // 设置表的字段名称
                sqlFieldInfo.setFieldName(field);
                // 设置bean的字段名称
                sqlFieldInfo.setPropertyName(propertyName);
                // 设置表的字段类型
                sqlFieldInfo.setSqlType(type);
                // 设置bean的字段类型(将sql类型转为java类型)
                sqlFieldInfo.setJavaType(SqlTypeToJavaTypeUtils.SqlTypeToJavaType(type));
                // 设置表的字段备注
                sqlFieldInfo.setComment(comment);
                // 设置表的字段是否自增长,忽略大小写
                sqlFieldInfo.setIsAutoIncrement(StrUtil.equals(extra,"auto_increment",true));

                sqlFieldInfoList.add(sqlFieldInfo);

                // 表是否有date日期类型
                if(ArrayUtil.contains(SqlTypeConstants.SQL_DATE_TYPE,type)){
                    sqlTableInfo.setHaveDate(true);
                }else {
                    sqlTableInfo.setHaveDate(false);
                }
                // 表是否有datetime时间类型
                if(ArrayUtil.contains(SqlTypeConstants.SQL_DATE_TIME_TYPE,type)){
                    sqlTableInfo.setHaveDateTime(true);
                }else {
                    sqlTableInfo.setHaveDateTime(false);
                }
                // 表是否有bigdecimal类型
                if(ArrayUtil.contains(SqlTypeConstants.SQL_DECIMAL_TYPE,type)){
                    sqlTableInfo.setHaveBigDecimal(true);
                }else {
                    sqlTableInfo.setHaveBigDecimal(false);
                }
                // 设置表的字段信息
                sqlTableInfo.setFieldList(sqlFieldInfoList);
                // 转换为格式化后的JSON字符串
                //log.info("sqlFieldInfoList:{}",JSONUtil.toJsonPrettyStr(sqlFieldInfoList));
            }
        } catch (Exception e) {
            log.error("读取表字段失败",e);
        }

    }


    /**
     * 读取表索引
     * @param sqlTableInfo
     * @return
     */
    private static List<SqlFieldInfo> getKeyIndexInfo(SqlTableInfo sqlTableInfo) {
        List<SqlFieldInfo> sqlFieldInfoList = new ArrayList();
        try {
            //1.获取数据库连接
            conn = JDBCUtils.getConnection();
            //2.获取prepareStatement对象
            ps = conn.prepareStatement(StrUtil.format(SqlConstants.SQL_SHOW_TABLE_INDEX,sqlTableInfo.getTableName()));
            //3.执行sql语句
            rs = ps.executeQuery();

            HashMap<String, SqlFieldInfo> tempMap = new HashMap();
            for (SqlFieldInfo sqlFieldInfo : sqlTableInfo.getFieldList()) {
                tempMap.put(sqlFieldInfo.getFieldName(),sqlFieldInfo);
            }


            //4.遍历查询结果,获取表字段
            while(rs.next()) {
                String keyName = rs.getString("key_name");
                Integer nonUnique = rs.getInt("non_unique");
                String columnName = rs.getString("column_name");
                List<SqlFieldInfo> KeyFieldList = sqlTableInfo.getKeyIndexMap().get(keyName);
                // 如果索引为空则创建
                if(KeyFieldList == null) {
                    KeyFieldList = new ArrayList();
                    sqlTableInfo.getKeyIndexMap().put(keyName, KeyFieldList);
                }
                KeyFieldList.add(tempMap.get(columnName));
            }
        } catch (Exception e) {
            log.error("读取索引失败:{}",e);
        }
        log.info("sqlFieldInfoList:{}",sqlFieldInfoList);
        return sqlFieldInfoList;
    }



}































